set doc off
set verify off
--
--    Program: CAPGLEXT_PERIOD_SETS.sql
--    Description:
--    Extract GL period sets information 
--
--    Revision:
--    1st     
--    Parameter: 1. set of books short name e.g. SOB_C999


--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
  l_count			number;
  l_processed_count	number;
  l_rejected_count	number;
  l_err_msg       	varchar2(2000);
  
  l_utl_file  		utl_file.file_type;
  l_filename  		varchar2(255);
  l_pathname  		varchar2(100);

  l_ngo			varchar2(20);
  l_set_of_books_id	number;
  
  cursor cur_cal (p_NGO VARCHAR2) is
	SELECT A.* 
	from gl_period_statuses A, GL_SETS_OF_BOOKS B
	WHERE SUBSTR(B.SHORT_NAME,5,4) = p_NGO
	AND A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID;
	
begin
  l_pathname := '/tmp';
  l_filename := 'CAPGL_PERIOD_STATUSES.dat';
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
  l_count := 0;
  l_processed_count := 0;
  l_rejected_count  := 0;
  
  l_ngo := '&1';
  
     
  for cur in cur_cal(l_ngo) loop 	
    -- output data
    utl_file.put_line( l_utl_file, 
		CUR.APPLICATION_ID                 ||CHR(9)||
		CUR.SET_OF_BOOKS_ID                ||CHR(9)||
		CUR.PERIOD_NAME                    ||CHR(9)|| 
		CUR.LAST_UPDATE_DATE      ||CHR(9)|| 
		CUR.LAST_UPDATED_BY                ||CHR(9)|| 
		CUR.CLOSING_STATUS                 ||CHR(9)||
		CUR.START_DATE            ||CHR(9)||
		CUR.END_DATE              ||CHR(9)||
		CUR.YEAR_START_DATE       ||CHR(9)||
		CUR.QUARTER_NUM                    ||CHR(9)||
		CUR.QUARTER_START_DATE    ||CHR(9)||
		CUR.PERIOD_TYPE                    ||CHR(9)||
		CUR.PERIOD_YEAR                    ||CHR(9)||
		CUR.EFFECTIVE_PERIOD_NUM           ||CHR(9)||
		CUR.PERIOD_NUM                     ||CHR(9)||
		CUR.ADJUSTMENT_PERIOD_FLAG         ||CHR(9)||
		CUR.CREATION_DATE		         ||CHR(9)||
		CUR.CREATED_BY                     ||CHR(9)||
		CUR.LAST_UPDATE_LOGIN              ||CHR(9)||
		CUR.ELIMINATION_CONFIRMED_FLAG     ||CHR(9)||
		CUR.ATTRIBUTE1                     ||CHR(9)||
		CUR.ATTRIBUTE2                     ||CHR(9)||
		CUR.ATTRIBUTE3                     ||CHR(9)||
		CUR.ATTRIBUTE4                     ||CHR(9)||
		CUR.ATTRIBUTE5                     ||CHR(9)||
		CUR.CONTEXT                        ||CHR(9)||
		CUR.CHRONOLOGICAL_SEQ_STATUS_CODE  
	);
    l_processed_count := l_processed_count + 1;  
  end loop;
	if l_processed_count = 0 then
	l_rejected_count := l_rejected_count + 1;
	l_err_msg := l_ngo || ': no calendar defined for the selected set of books or no such set of books';
	end if;
      
  -- insert into log table
  insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_PERIOD_STATUSES', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
  
  commit;
  
  utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
